


set search_path = cal, pg_catalog;

create or replace function populate_days(def_name varchar, startdt date, enddt date)
returns bool
as $$

	/************************************************
	*
	* Copyright 2008 David B. Spadea
	* All rights reserved
	*
	* http://www.spadea.net
	* 
	* This program and all supporting code 
	* is hereby released under the terms 
	* of the GNU General Public License version 3
	*
	************************************************/

begin

	perform * from definitions where name = def_name;

	if not found then
		raise exception 'The named definition does not exist.';
		rollback;
		return false;
	end if;

	perform * from definitions where name = def_name and base_def is not null;

	if found then
		raise exception 'The named definition references another calendar so it cannot hold its own schedule.';
		rollback;
		return false;
	end if;

	create temporary table caldates
	(
		  defid		bigint not null
		, date		date not null
		, prev_bus_dt	date null
		, curr_bus_dt	date null
		, next_bus_dt	date null
		, business_date	bool default false
		, weekend	bool default false
		, holiday	bool default false
		, holiday_id	bigint null
		, daynum	int
	) on commit drop;


	insert into caldates (defid, date, daynum)
	select	  def.id
		, caldate
		, extract (dow from caldate)
	from definitions def,
	(
		select startdt + offsetdays as caldate
		from generate_series(-7, (enddt - startdt) + 7) offsetdays
	) dates
	where 
		def.name = def_name;

	update caldates
	set weekend = true
	from definitions def
	where daynum in ( def.weekend_start, def.weekend_end )
	  and def.id = caldates.defid;

	update caldates 
	set holiday_id = hol.id,
	    holiday = true
	from holidays hol
	where hol.definition_id = caldates.defid
	  and hol.date = caldates.date;

	update caldates 
	set   curr_bus_dt = date
	    , business_date = true
	where holiday = false
	  and weekend = false;

	update caldates
	set curr_bus_dt = (select max(date)
			   from caldates cd
			   where cd.date < caldates.date
			     and business_date = true)
	where curr_bus_dt is null;

	update caldates
	set prev_bus_dt = (select max(date) 
			   from caldates cd
			   where cd.date < caldates.curr_bus_dt
			     and business_date = true
			  );

	update caldates
	set next_bus_dt = (select min(date) 
			   from caldates cd
			   where cd.date > caldates.curr_bus_dt
			     and business_date = true
			  );


	delete 
	from days
	using caldates
	where days.date between startdt and enddt
	  and caldates.date = days.date
	  and caldates.defid = days.definition_id;

	insert into days(
		  definition_id
		, date
		, prev_bus_dt
		, curr_bus_dt
		, next_bus_dt
		, business_date
		, weekend
		, holiday
		, holiday_id
		, day_of_week
	)
	select 
		  defid
		, date
		, prev_bus_dt
		, curr_bus_dt
		, next_bus_dt
		, business_date
		, weekend
		, holiday
		, holiday_id
		, daynum
	from caldates
	where date between startdt and enddt;

	return true;

end;


$$ volatile language plpgsql;
